package net.huashitong.supervision.report.dao;

import com.sirdc.modules.core.dao.CrudDao;
import net.huashitong.supervision.report.dto.InsideReportDTO;
import net.huashitong.supervision.report.dto.LoginReportDTO;
import net.huashitong.supervision.report.dto.TaskReportDTO;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class ReportDao extends CrudDao {


    //内部督查报表
    public List<InsideReportDTO> getInsideTask(String startDate , String endDate ){


        StringBuffer sql = new StringBuffer();
        sql.append(" select t1.create_date  createDate ,t1.create_time createTime ,t1.remind_date  remindDate,t1.time_round  timeRound , ");
        sql.append("  (case when t1.task_state='0' then '代签收'   when '1' then '已签收'   when '2' then '已反馈'  when '3' then '退回'   when '4' then '完成'  when '5' then '已签收' else  '不知道' end ) as taskState,  ");
        sql.append(" t2.name   ,t3.task_name taskName  ,t4.sub_task_name subTaskName ");
        sql.append(" from  inside_user_task t1 ,t_sys_user t2   , inside_task_main t3,inside_task_sub t4");
        sql.append(" where ");
        sql.append(" t1.user_id= t2.sys_id  and");
        sql.append(" t1.sub_task_id = t4.sys_id and");
        sql.append(" t3.sys_id= t4.task_main_id and");
        sql.append(" STR_TO_DATE(t1.create_date,'%Y-%m-%d')>STR_TO_DATE('"+startDate+"','%Y-%m-%d') ");
        sql.append(" and STR_TO_DATE( t1.create_date, '%Y-%m-%d' ) <= STR_TO_DATE( '"+endDate+"', '%Y-%m-%d' ) ");
        sql.append(" ORDER BY t1.sys_id asc ");

        SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString());
        sqlQuery.setResultTransformer(Transformers.aliasToBean(InsideReportDTO.class));
        List<InsideReportDTO> list = (List<InsideReportDTO>) sqlQuery.list();

        return list;


    }


    /**
     * 普通任务
     * @param stratDate
     * @param endDate
     * @return
     */
    public List<TaskReportDTO> getTaskListReport(String stratDate, String endDate)
    {
        StringBuffer sql = new StringBuffer();
        sql.append(" select   create_date createDate ,create_time createTime ,time_limit timeLimit, dept_name deptName , " );
        sql.append(" (case type  when '0' then '未签收' when '1' then '未反馈'  when '2' then '已反馈' when '3' then '退回' when '4' then '已完成' when '8' then '回退单-已反馈'  when '9' then '回退单-完成'  else '退回' end  )  as  type ,");
        sql.append(" time_round timeRound ,task_sub_name taskSubName,task_main_name taskMainName ");
        sql.append(" from  t_task_unit ");
        sql.append(" where task_main_id in ");
        sql.append(" (select  sys_id  from  t_task_main  where   ");
        sql.append(" STR_TO_DATE(create_date,'%Y-%m-%d')>STR_TO_DATE('"+stratDate+"','%Y-%m-%d')  ");
        sql.append("  and STR_TO_DATE( create_date, '%Y-%m-%d' ) <= STR_TO_DATE( '"+endDate+"', '%Y-%m-%d' ) ) ");
        sql.append(" ORDER BY sys_id desc ");
        SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString());
        sqlQuery.setResultTransformer(Transformers.aliasToBean(TaskReportDTO.class));
        List<TaskReportDTO> list = (List<TaskReportDTO>) sqlQuery.list();

        return list;

    }


    /**
     * 登陆报表
     * @return
     */

    public List<LoginReportDTO> getLoginList (String startDate, String endDate)
    {

        /*
        select  t1.name , (select  count(1) from t_sys_login_log t2 where t2.user_id = t1.sys_id and  STR_TO_DATE( create_date, '%Y-%m-%d' ) >= STR_TO_DATE( '2021-3-1', '%Y-%m-%d' )
	    AND STR_TO_DATE( create_date, '%Y-%m-%d' ) <= STR_TO_DATE( '2021-6-1', '%Y-%m-%d' ) )
	    from t_sys_user t1;
         */
        StringBuffer sql = new StringBuffer();
        sql.append("select  t1.name ,");
        sql.append(" (select  count(1) from t_sys_login_log t2  ");
        sql.append(" where t2.user_id = t1.sys_id and   ");
        sql.append(" STR_TO_DATE( create_date, '%Y-%m-%d' ) >= STR_TO_DATE( '"+startDate+"', '%Y-%m-%d' ) ");
        sql.append(" and STR_TO_DATE( create_date, '%Y-%m-%d' ) <= STR_TO_DATE( '"+endDate+"', '%Y-%m-%d' ) ) loginTime ");
        sql.append(" from t_sys_user t1 ");
        SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString());
        sqlQuery.setResultTransformer(Transformers.aliasToBean(LoginReportDTO.class));
        List<LoginReportDTO> list = (List<LoginReportDTO>) sqlQuery.list();

        return list;


    }


}
